﻿//
//  Monkey Manager
//  Copyright (C) 2012 Victor Tereschenko
//
//  This program is free software: you can redistribute it and/or modify
//  it under the terms of the GNU General Public License as published by
//  the Free Software Foundation, either version 3 of the License, or
//  (at your option) any later version.
//
//  This program is distributed in the hope that it will be useful,
//  but WITHOUT ANY WARRANTY; without even the implied warranty of
//  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//  GNU General Public License for more details.
//
//  You should have received a copy of the GNU General Public License
//  along with this program.  If not, see <http://www.gnu.org/licenses/>.
//     
// ========================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace ManagerDB.SQLiteDB.Queries
{
    /// <summary>
    /// Helps to build select query according to filter.
    /// </summary>
    class SelectQueryBuilder
    {
        public static SQLiteCommand Create(TabController tagController, SQLiteConnection connection, Filter filter)
        {
            var command = new SQLiteCommand(connection);
            var stringBuilder = new StringBuilder("SELECT rowid, fileName, year, day, month, title, description, count, lastPosition, locationID, TTH FROM files ");
            SQLBuilder sqlBuilder = new SQLBuilder();
            addFileFilter(command, stringBuilder, sqlBuilder, filter.FileStatus);
            addTTHFilter(command, stringBuilder, filter.TTH, sqlBuilder);
            addSelectWatchedFilter(command, stringBuilder, filter.WatchStatus, sqlBuilder);
            addSelectTextFilter(command, stringBuilder, filter.Text, sqlBuilder);
            if (!addSelectTags(tagController, command, stringBuilder, filter.Tags, sqlBuilder))
                return null;
            addSorting(command, stringBuilder, filter.SortMode, filter.SortColumn);
            addLimitAndOffset(command, stringBuilder, filter.Limit, filter.Offset);
            command.CommandText = stringBuilder.ToString();

            return command;
        }

        private static void addFileFilter(SQLiteCommand command, StringBuilder sb, SQLBuilder sqlBuilder, FileStatus fileStatus)
        {
            if (fileStatus == FileStatus.All)
                return;
            if (!sqlBuilder.WhereInserted)
            {
                sb.Append(" WHERE ");
                sqlBuilder.WhereInserted = true;
            }
            else
                sb.Append(" AND ");

            switch (fileStatus)
            {
                case FileStatus.Deleted:
                    sb.Append(" fileName=''");
                    break;
                case FileStatus.Present:
                    sb.Append(" fileName!=''");
                    break;
            }
        }

        private static void addTTHFilter(SQLiteCommand command, StringBuilder sb, string tth, SQLBuilder sqlBuilder)
        {
            if (tth == null)
                return;

            if (!sqlBuilder.WhereInserted)
            {
                sb.Append(" WHERE ");
                sqlBuilder.WhereInserted = true;
            }
            else
                sb.Append(" AND ");

            if (tth != "")
            {
                sb.Append(" TTH=@TTH");
                command.Parameters.Add(new SQLiteParameter("@TTH", tth));
            }
            else
                sb.Append(" (TTH='' OR TTH IS NULL) ");
        }

        private static void addSelectWatchedFilter(SQLiteCommand command, StringBuilder sb, WatchStatus watchStatus, SQLBuilder sqlBuilder)
        {
            switch (watchStatus)
            {
                case WatchStatus.All:
                    // do nothing
                    break;
                case WatchStatus.PartlyWatched:
                    if (!sqlBuilder.WhereInserted)
                    {
                        sb.Append(" WHERE ");
                        sqlBuilder.WhereInserted = true;
                    }
                    else
                        sb.Append(" AND ");

                    sb.Append(" count > 0.0 AND count < 1.0 ");
                    break;
                case WatchStatus.Unwatched:
                    if (!sqlBuilder.WhereInserted)
                    {
                        sb.Append(" WHERE ");
                        sqlBuilder.WhereInserted = true;
                    }
                    else
                        sb.Append(" AND ");

                    sb.Append(" count < 1.0 ");
                    break;
                case WatchStatus.Watched:
                    if (!sqlBuilder.WhereInserted)
                    {
                        sb.Append(" WHERE ");
                        sqlBuilder.WhereInserted = true;
                    }
                    else
                        sb.Append(" AND ");

                    sb.Append(" count >= 1.0 ");
                    break;
            }
        }

        private static void addSelectTextFilter(SQLiteCommand command, StringBuilder sb, string text, SQLBuilder sqlBuilder)
        {
            if (text == null || text == "")
                return;

            if (!sqlBuilder.WhereInserted)
            {
                sb.Append(" WHERE ");
                sqlBuilder.WhereInserted = true;
            }
            else
                sb.Append(" AND ");

            sb.Append(" title LIKE @text");
            command.Parameters.Add(new SQLiteParameter("@text", "%" + text + "%"));
        }

        /// <summary>
        /// Adds select by tags SQL selction.
        /// </summary>
        /// <param name="command"></param>
        /// <param name="sb"></param>
        /// <param name="tags"></param>
        private static bool addSelectTags(TabController tagController, SQLiteCommand command, StringBuilder sb, string tags, SQLBuilder sqlBuilder)
        {
            if (tags == null)
                return true;
            string[] tagItems = StoredFileController.GetTags(tags);
            if (tagItems.Length == 0)
                return true;

            int i = 0;
            foreach (string tag in tagItems)
            {
                string trimmedTag = tag.Trim();
                if (trimmedTag.Length == 0)
                    continue;
                long tagId = tagController.GetTagID(trimmedTag);
                if (tagId < 0)
                    return false;
                if (i == 0)
                {
                    if (!sqlBuilder.WhereInserted)
                    {
                        sb.Append(" WHERE ");
                        sqlBuilder.WhereInserted = true;
                    }
                    else
                        sb.Append(" AND ");
                    sb.Append(" rowid IN (");
                }
                else
                    sb.Append(" INTERSECT");
                sb.Append(" SELECT fileId FROM fileTags WHERE tagId=@tagId" + i.ToString());
                command.Parameters.Add(new SQLiteParameter("@tagId" + i.ToString(), tagId));
                ++i;
            }
            if (i > 0)
                sb.Append(")");

            return true;
        }

        private static void addLimitAndOffset(SQLiteCommand command, StringBuilder sb, int limit, int offset)
        {
            if (limit <= 0)
                return;

            sb.Append(" LIMIT @limit");
            command.Parameters.Add(new SQLiteParameter("@limit", limit));

            if (offset <= 0)
                return;
            sb.Append(" OFFSET @offset");
            command.Parameters.Add(new SQLiteParameter("@offset", offset));
        }

        static void addSorting(SQLiteCommand command, StringBuilder sb, SortMode sortMode, SortColumn sortColumn)
        {
            if (sortMode == SortMode.NoSort)
                return;
            if (sortMode == SortMode.Random)
                sb.Append(" ORDER BY RANDOM()");
            switch (sortColumn)
            {
                case SortColumn.RowId:
                    switch (sortMode)
                    {
                        case SortMode.Ascending:
                            sb.Append(" ORDER BY rowid ASC");
                            break;
                        case SortMode.Descending:
                            sb.Append(" ORDER BY rowid DESC");
                            break;
                    }
                    break;
                case SortColumn.Name:
                    switch (sortMode)
                    {
                        case SortMode.Ascending:
                            sb.Append(" ORDER BY title ASC");
                            break;
                        case SortMode.Descending:
                            sb.Append(" ORDER BY title DESC");
                            break;
                    }
                    break;
                case SortColumn.Date:
                    // TODO: implment
                    break;
            }
        }
    }

}
